﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace VisaCRM.Common.Helper
{
    public static class QueryProvider
    {
        /// <summary>
        /// Country related Query
        /// </summary>
        /// <returns></returns>
        public static string MakeSQLQueryToFetchCountryList()
        {
            return "SELECT [Country_ID],[Country_Name],[Country_Nationality]  FROM [L_Country]";
        }

        public static string MakeSQLQueryToInsertCountryToDatabase(string country,string nationality,int eu,int migration)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("insert into l_country ");
            stringBuilder.Append("(Country_Name,Country_Nationality,Country_EU,Country_Migration)  Values(");
            stringBuilder.Append(" '" +country + "',");
            stringBuilder.Append(" '" + nationality + "',");
            stringBuilder.Append(eu + ",");
            stringBuilder.Append(migration + ")");
            return stringBuilder.ToString();
        }

        /// <summary>
        /// Industry related query
        /// </summary>
        /// <returns></returns>
        public static string MakeSQLQueryToFetchIndustryList()
        {
            return "SELECT [Industry_ID],[Industry_Name]  FROM [L_Industry]";
        }

        public static string MakeSQLQueryToInsertIdustryToDatabase(string industry)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("insert into l_industry ");
            stringBuilder.Append("(Industry_Name) Values(");
            stringBuilder.Append("'" + industry + "'");
            stringBuilder.Append(")");
            return stringBuilder.ToString();
        }

        public static string MakeSQLQueryToEditIndustryToDatabase(string industry, int rowid)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("update l_industry set ");
            stringBuilder.Append("Industry_Name = '" +industry+ "'");
            stringBuilder.Append("where Industry_ID = "+rowid);
            return stringBuilder.ToString();
        }

        public static string MakeSQLQueryToDeleteIndustryFromDatabase(int rowid)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("delete  from l_industry where Industry_ID="+rowid);
            return stringBuilder.ToString();
        }

        /// <summary>
        /// Where Hear related query
        /// </summary>
        public static string MakeSQLQueryToFetchWhereHearList()
        {
            return "SELECT [WhereHear_ID],[WhereHear_Text],[WhereHear_Website] FROM [L_WhereHear]";
        }

        public static string MakeSQLQueryToInsertWhereHearToDatabase(string wherehear,int wherehearwebsite)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("insert into l_wherehear ");
            stringBuilder.Append("(WhereHear_Text,WhereHear_Website) Values(");
            stringBuilder.Append("'" + wherehear + "',");
            stringBuilder.Append(wherehearwebsite + ")");
            return stringBuilder.ToString();
        }

        public static string MakeSQLQueryToEditWhereHearToDatabase(string wherehear,int wherehearwebsite, int rowid)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("update l_wherehear set ");
            stringBuilder.Append(" WhereHear_Text = '" + wherehear + "',");
            stringBuilder.Append(" WhereHear_Website = " + wherehearwebsite);
            stringBuilder.Append(" where WhereHear_ID = " + rowid);
            return stringBuilder.ToString();
        }

        public static string MakeSQLQueryToDeleteWhereHearFromDatabase(int rowid)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("delete  from l_wherehear where WhereHear_ID=" + rowid);
            return stringBuilder.ToString();
        }

        /// <summary>
        /// Product Type related query
        /// </summary>
        public static string MakeSQLQueryToFetchProductTypesList()
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("SELECT [ProductType_ID],[ProductType_Name],[ProductType_DestinationID],[ProductType_DepartmentID],[ProductType_StageDiagramID]");
            stringBuilder.Append(",[ProductType_MFBGroupID],[ProductType_SLSID],[ProductType_ChargeGroupID],[ProductType_EventGroupID],[ProductType_WebLoopID],");
            stringBuilder.Append("[ProductType_FastTrack],[ProductType_DTCategory],[ProductType_CalcSet],[ProductType_TargetGroupID],[ProductType_BranchID],");
            stringBuilder.Append("[PRODUCTTYPE_DTDISABLED],[ProductType_ResidenceID] FROM [GLOBAL].[dbo].[L_ProductTypes]");
            return stringBuilder.ToString();
        }

        public static string MakeSQLQueryToInsertProductTypeToDatabase(string productname,int desId,int depId,int stgId,int cgId,int egId,int BId,int resId)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("insert into L_ProductTypes ");
            stringBuilder.Append("([ProductType_Name],[ProductType_DestinationID] ,[ProductType_DepartmentID],");
            stringBuilder.Append("[ProductType_StageDiagramID],[ProductType_MFBGroupID],[ProductType_SLSID],[ProductType_ChargeGroupID],[ProductType_EventGroupID],");
            stringBuilder.Append("[ProductType_WebLoopID],[ProductType_FastTrack],[ProductType_DTCategory],[ProductType_CalcSet],[ProductType_TargetGroupID],");
            stringBuilder.Append("[ProductType_BranchID],[PRODUCTTYPE_DTDISABLED],[ProductType_ResidenceID] )");
            stringBuilder.Append("Values('" + productname + "'," + desId + "," + depId + ",");
            stringBuilder.Append(stgId + ",1,1," + cgId + "," + egId + ",");
            stringBuilder.Append("1,1,1,1,1,");
            stringBuilder.Append(BId + ",''," + resId + ")");
            return stringBuilder.ToString();
        }

        public static string MakeSQLQueryToEditProductTypeToDatabase(int rowid, string productname, int desId, int depId, int stgId, int cgId, int egId, int BId, int resId)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("update L_ProductTypes set ");
            stringBuilder.Append(" [ProductType_Name] = '" + productname + "',");
            stringBuilder.Append(" [ProductType_DestinationID] =" + desId + ",");
            stringBuilder.Append(" [ProductType_DepartmentID] =" + depId + ",");
            stringBuilder.Append(" [ProductType_StageDiagramID] =" + stgId + ",");
            stringBuilder.Append(" [ProductType_MFBGroupID] =" + 1 + ",");
            stringBuilder.Append(" [ProductType_SLSID] =" + 1 + ",");
            stringBuilder.Append(" [ProductType_ChargeGroupID] =" + cgId + ",");
            stringBuilder.Append(" [ProductType_EventGroupID] =" + egId + ",");
            stringBuilder.Append(" [ProductType_WebLoopID] =" + 1 + ",");
            stringBuilder.Append(" [ProductType_FastTrack] =" + 1 + ",");
            stringBuilder.Append(" [ProductType_DTCategory] =" + 1 + ",");
            stringBuilder.Append(" [ProductType_CalcSet] =" + 1 + ",");
            stringBuilder.Append(" [ProductType_TargetGroupID] =" + 1 + ",");
            stringBuilder.Append(" [ProductType_BranchID] =" + BId + ",");
            stringBuilder.Append(" [PRODUCTTYPE_DTDISABLED] =" + "''" + ",");
            stringBuilder.Append(" [ProductType_ResidenceID] =" + resId);
            stringBuilder.Append("where ProductType_ID = " + rowid);
            return stringBuilder.ToString();
        }

        public static string MakeSQLQueryToDeleteProductTypeFromDatabase(string name)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("delete  from L_ProductTypes where ProductType_Name= '"+name+"'");
            return stringBuilder.ToString();
        }

        public static string MakeSQLQueryToFetchCurrentDestination(int id)
        {
            return "SELECT [Country_ID], [Country_Name] FROM [L_Country]where Country_ID= " + id;
        }

        public static string MakeSQLQueryToFetchDepartmentList()
        {
            return "Select Department_ID, Department_Name,[Department_Products],[Department_BranchID] from L_Departments";
        }

        public static string MakeSQLQueryToFetchCurrentDepartment(int id)
        {
            return "SELECT Department_ID, Department_Name FROM [L_Departments]where Department_ID= " + id;
        }

        public static string MakeSQLQueryToFetchStageDiagramList()
        {
            return "Select StageDiagram_ID, StageDiagram_Name from [L_StageDiagram]";
        }

        public static string MakeSQLQueryToFetchCurrentStageDiagram(int id)
        {
            return "SELECT StageDiagram_ID, StageDiagram_Name FROM [L_StageDiagram]where StageDiagram_ID= " + id;
        }

        public static string MakeSQLQueryToFetchChargeGroupList()
        {
            return "Select ChargeGroup_ID, ChargeGroup_Name from [A_ChargeGroups]";
        }

        public static string MakeSQLQueryToFetchCurrentChargeGroup(int id)
        {
            return "SELECT ChargeGroup_ID, ChargeGroup_Name FROM [A_ChargeGroups]where ChargeGroup_ID= " + id;
        }

        public static string MakeSQLQueryToFetchEventGroupList()
        {
            return "Select EventGroup_ID, EventGroup_Name from [I_EventGroups]";
        }

        public static string MakeSQLQueryToFetchBranchList()
        {
            return "Select Branch_ID, Branch_Name from [L_Branch]";
        }


        public static string MakeSQLQueryToFetchCurrentBranch(int id)
        {
            return "SELECT Branch_ID, Branch_Name FROM [L_Branch]where Branch_ID= " + id;
        }

        public static string MakeSQLQueryToFetchCurrentEventGroup(int id)
        {
            return "SELECT EventGroup_ID, EventGroup_Name FROM [I_EventGroups]where EventGroup_ID= " + id; 
        }

        /// <summary>
        /// Title related query
        /// </summary>
        public static string MakeSQLQueryToFetchTitleList()
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("SELECT [Title_ID],[Title_Text],[Title_Gender],[Title_Discontinued]");
            stringBuilder.Append("FROM [GLOBAL].[dbo].[L_Titles]");
            return stringBuilder.ToString();
        }

        public static string MakeSQLQueryToInsertTitleToDatabase(string ttext, int tgen)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("insert into L_Titles ");
            stringBuilder.Append("([Title_Text],[Title_Gender] ,[Title_Discontinued])");
            stringBuilder.Append(" Values ('" + ttext + "'," + tgen + "," );
            stringBuilder.Append("0)");
            return stringBuilder.ToString();
        }

        public static string MakeSQLQueryToDeleteTitleFromDatabase(int rowid)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("delete  from L_Titles where Title_ID= " + rowid);
            return stringBuilder.ToString();
        }

        public static string MakeSQLQueryToEditTitleToDatabase(int rowid, string ttext, int tgen)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("update L_Titles set ");
            stringBuilder.Append(" [Title_Text] = '" + ttext + "',");
            stringBuilder.Append(" [Title_Gender] =" + tgen);
            stringBuilder.Append("where Title_ID = " + rowid);
            return stringBuilder.ToString();
        }

        /// <summary>
        /// Qualification related query
        /// </summary>
        public static string MakeSQLQueryToFetchQualificationList()
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("SELECT [Qual_ID],[Qual_Name],[Qual_Degree],[Qual_Country]");
            stringBuilder.Append("FROM [GLOBAL].[dbo].[L_Qualifications]");
            return stringBuilder.ToString();
        }

        public static string MakeSQLQueryToDeleteQualificationFromDatabase(int rowid)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("delete  from L_Qualifications where Qual_ID= " + rowid);
            return stringBuilder.ToString();
        }

        public static string MakeSQLQueryToEditQualificationToDatabase(string qualificationName, int qualificationDegree, int rowid)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("update L_Qualifications set ");
            stringBuilder.Append(" Qual_Name = '" + qualificationName + "',");
            stringBuilder.Append(" Qual_Degree = " + qualificationDegree);
            stringBuilder.Append(" where Qual_ID = " + rowid);
            return stringBuilder.ToString();
        }

        public static string MakeSQLQueryToInsertQualificationToDatabase(string qualificationName, int qualificationDegree)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("insert into L_Qualifications ");
            stringBuilder.Append("(Qual_Name,Qual_Degree) Values(");
            stringBuilder.Append("'" + qualificationName + "',");
            stringBuilder.Append(qualificationDegree + ")");
            return stringBuilder.ToString();
        }

        /// <summary>
        /// Consultant related query
        /// </summary>
        public static string MakeSQLQueryToFetchConsultantList()
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("SELECT [Con_ID],[Con_Name],[Con_JobTitle],[Con_Status],[Con_Department],[Con_BranchID],[Con_DDI],");
            stringBuilder.Append("[Con_DDIUK],[Con_Email],[Con_SupervisorID],[Con_Accreditation],[Con_SecurityLevel]");
            stringBuilder.Append("FROM [GLOBAL].[dbo].[L_Consultant]");
            return stringBuilder.ToString();
        }

        public static string MakeSQLQueryToFetchAccessLevelList()
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("SELECT [Security_ID],[Security_Name] ");
            stringBuilder.Append("FROM [GLOBAL].[dbo].[l_Security]");
            return stringBuilder.ToString();
        }

        public static string MakeSQLQueryToFetchCurrentSupervisor(int id)
        {
            return "SELECT Con_ID, Con_Name FROM [L_Consultant]where Con_ID= " + id;
        }

        public static string MakeSQLQueryToFetchSupervisorList()
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("SELECT [Con_ID],[Con_Name]");
            stringBuilder.Append("FROM [GLOBAL].[dbo].[L_Consultant]");
            stringBuilder.Append("where Con_Status = 1 ");
            return stringBuilder.ToString();
        }

        public static string MakeSQLQueryToFetchCurrentAccessLevel(int id)
        {
            return "SELECT Security_ID, Security_Name FROM [l_Security]where Security_ID= " + id;
        }

        public static string MakeSQLQueryToDeleteConsultantFromDatabase(int rowid)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("delete  from L_Consultant where Con_ID= " + rowid);
            return stringBuilder.ToString();
        }

        public static string MakeSQLQueryToEditConsultantToDatabase(int rowid, string ConName, int conbranchId, string conAccredionDetail, int condep, int consupid, string conEmail, string conddi, string conddiUk, string conjobTit, int consecLevel, int constatus)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("update L_Consultant set ");
            stringBuilder.Append(" Con_Name = '" + ConName + "',");
            stringBuilder.Append(" Con_JobTitle = '" + conjobTit + "',");
            stringBuilder.Append(" Con_Status = " + constatus + ",");
            stringBuilder.Append(" Con_Department = " + condep + ",");
            stringBuilder.Append(" Con_BranchID = " + conbranchId + ",");
            stringBuilder.Append(" Con_DDI = '" + conddi + "',");
            stringBuilder.Append(" Con_DDIUK = '" + conddiUk + "',");
            stringBuilder.Append(" Con_Email = '" + conEmail + "',");
            stringBuilder.Append(" Con_SupervisorID = " + consupid + ",");
            stringBuilder.Append(" Con_Accreditation = '" + conAccredionDetail + "',");
            stringBuilder.Append(" Con_SecurityLevel = " + consecLevel);
            stringBuilder.Append(" where Con_ID = " + rowid);
            return stringBuilder.ToString();
        }

        public static string MakeSQLQueryToInsertConsultantToDatabase(string ConName, int conbranchId, string conAccredionDetail, int condep, int consupid, string conEmail, string conddi, string conddiUk, string conjobTit, int consecLevel, int constatus)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("insert into L_Consultant ");
            stringBuilder.Append("(Con_Name,Con_JobTitle,Con_Status,Con_Department,Con_BranchID,Con_DDI,Con_DDIUK,Con_Email,Con_SupervisorID,Con_Accreditation,Con_SecurityLevel) Values(");
            stringBuilder.Append("'" + ConName + "',");
            stringBuilder.Append("'" + conjobTit + "',");
            stringBuilder.Append( + constatus + ",");
            stringBuilder.Append( + condep + ",");
            stringBuilder.Append( + conbranchId + ",");
            stringBuilder.Append("'" + conddi + "',");
            stringBuilder.Append("'" + conddiUk + "',");
            stringBuilder.Append("'" + conEmail + "',");
            stringBuilder.Append( + consupid + ",");
            stringBuilder.Append("'" + conAccredionDetail + "',");
            stringBuilder.Append(consecLevel + ")");
            return stringBuilder.ToString();
        }
    }
}
